﻿namespace BLL
{
    using DAL;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class UserController
    {
        private ConnectionManager connMgr = new ConnectionManager();

        public void CreateUser(string UserName, int idTeam, int idGestor, string Login)
        {
            SqlCommand addUser = new SqlCommand("dbo.AddUsuario");
            addUser.CommandType = CommandType.StoredProcedure;
            addUser.Parameters.AddWithValue("@Nome", UserName);
            addUser.Parameters.AddWithValue("@idTime", idTeam);
            addUser.Parameters.AddWithValue("@idGestor", idGestor);
            addUser.Parameters.AddWithValue("@Login", Login);
            this.connMgr.doCommand(addUser);
        }

        public int GetGestorID(int UserID)
        {
            SqlCommand qryGestorID = new SqlCommand("SELECT idGestor FROM Usuarios WHERE idUsuario = @UserID");
            qryGestorID.Parameters.AddWithValue("@UserID", UserID);
            DataRow dr = this.connMgr.doReader(qryGestorID).Rows[0];
            return Convert.ToInt32(dr[0]);
        }

        public string GetGestorLogin(int GestorID)
        {
            SqlCommand qryGestorLogin = new SqlCommand("dbo.QryUsuarioLogin");
            qryGestorLogin.CommandType = CommandType.StoredProcedure;
            qryGestorLogin.Parameters.AddWithValue("@UserID", GestorID);
            DataRow dr = this.connMgr.doReader(qryGestorLogin).Rows[0];
            return dr[0].ToString();
        }

        public DataTable GetTeamUsers(int idTeam)
        {
            SqlCommand qryX = new SqlCommand();
            if (idTeam != 0)
            {
                qryX.CommandText = @"SELECT U.idUsuario, U.[Login], U.idGestor, U.Nome, T.DescTime, U2.Nome as Gestor
                                     FROM Usuarios AS U
                                     INNER JOIN Times AS T ON U.idTime = T.idTime
                                     INNER JOIN Usuarios AS U2 ON U.idGestor = U2.idUsuario
                                     WHERE U.idTime = @idTeam AND U.Active = 1 ORDER BY Nome";
                qryX.Parameters.AddWithValue("@idTeam", idTeam);
            }
            else
            {
                qryX.CommandText = "SELECT U.idUsuario, U.[Login], U.idGestor, U.Nome, T.DescTime, U2.Nome as Gestor\r\n                                     FROM Usuarios AS U\r\n                                     INNER JOIN Times AS T ON U.idTime = T.idTime\r\n                                     INNER JOIN Usuarios AS U2 ON U.idGestor = U2.idUsuario WHERE U.Active = 1 ORDER BY Nome";
            }
            return this.connMgr.doReader(qryX);
        }

        public int GetUserID(string UserName)
        {
            SqlCommand qryUserID = new SqlCommand("dbo.QryUsuarioID");
            qryUserID.CommandType = CommandType.StoredProcedure;
            qryUserID.Parameters.AddWithValue("@Login", UserName);
            DataRow dr = this.connMgr.doReader(qryUserID).Rows[0];
            return Convert.ToInt32(dr[0]);
        }

        public string GetUserLogin(int UserID)
        {
            SqlCommand qryUsername = new SqlCommand("SELECT [Login] FROM Usuarios WHERE idUsuario = @idUsuario");
            qryUsername.Parameters.AddWithValue("@idUsuario", UserID);
            DataRow dr = this.connMgr.doReader(qryUsername).Rows[0];
            return dr[0].ToString();
        }

        public string GetUserName(int UserID)
        {
            SqlCommand qryUsername = new SqlCommand("SELECT Nome FROM Usuarios WHERE idUsuario = @idUsuario");
            qryUsername.Parameters.AddWithValue("@idUsuario", UserID);
            DataRow dr = this.connMgr.doReader(qryUsername).Rows[0];
            return dr[0].ToString();
        }

        public int GetUserTeamID(int UserID)
        {
            SqlCommand qryUserTeamID = new SqlCommand("dbo.QryUsuarioTimeID");
            qryUserTeamID.CommandType = CommandType.StoredProcedure;
            qryUserTeamID.Parameters.AddWithValue("@UserID", UserID);
            DataRow dr = this.connMgr.doReader(qryUserTeamID).Rows[0];
            return Convert.ToInt32(dr[0]);
        }

        public void UpdateUser(int idUsuario, int idTime, int idGestor, string Login, string Nome)
        {
            SqlCommand qryX = new SqlCommand("UPDATE [dbo].[Usuarios] SET [Login] = @Login, [idTime] = @idTime, [idGestor] = @idGestor, [Nome] = @Nome WHERE idUsuario = @idUsuario");
            qryX.Parameters.AddWithValue("@idUsuario", idUsuario);
            qryX.Parameters.AddWithValue("@idTime", idTime);
            qryX.Parameters.AddWithValue("@idGestor", idGestor);
            qryX.Parameters.AddWithValue("@Login", Login);
            qryX.Parameters.AddWithValue("@Nome", Nome);
            this.connMgr.doCommand(qryX);
        }
    }
}

